package com.hethongviet.vending.helper.db;

import java.util.ArrayList;
import java.util.List;

import com.hethongviet.vending.Utils;
import com.hethongviet.vending.model.Product;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class ProductSQLite {
	public static final String TABLE_NAME = "products";

	public static final String COLUMN_ID_DB = "idDB";

	public static final String COLUMN_CODE = "code";
	public static final String COLUMN_NAME = "name";

	public static final String COLUMN_DESCRIPTION = "description";
	public static final String COLUMN_GROUP_ID = "group_id";
	public static final String COLUMN_GROUP = "group_name";
	public static final String COLUMN_PRICE = "price";

	// Database fields
	private SQLiteDatabase _database;
	private MySQLiteHelper _dbHelper;
	private String[] _allColumns = { COLUMN_ID_DB, COLUMN_CODE,
			COLUMN_DESCRIPTION, COLUMN_GROUP, COLUMN_NAME, COLUMN_PRICE, COLUMN_GROUP_ID };
	public static final String STRING_COLUMNS = COLUMN_ID_DB
			+ " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "
			+ COLUMN_CODE + " text, " + COLUMN_DESCRIPTION + " text, " + COLUMN_GROUP + " text, "
			+ COLUMN_NAME + " text, " + COLUMN_PRICE + " integer, "
			+ COLUMN_GROUP_ID + " integer";

	public ProductSQLite(Context context) {
		_dbHelper = new MySQLiteHelper(context, null, null);
	}

	public void open() throws SQLException {
		_database = _dbHelper.getWritableDatabase();
	}

	public void close() {
		_dbHelper.close();
	}

	private boolean dataExist(Product product, Cursor cursor) {
		if (cursor == null) {
			return false;
		}
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
			long idDB = cursor.getLong(cursor.getColumnIndex(COLUMN_ID_DB));
			if (idDB == product.getIdDB())
				return true;
		}
		return false;
	}

	/**
	 * @param product
	 * @return row id -1: insert fail
	 */
	public long insert(Product product) {
		open();

		Utils.debug("insert -- " + product.getName());
		Cursor cursor = _database.rawQuery("SELECT * FROM " + TABLE_NAME
				+ " WHERE " + COLUMN_ID_DB + " = " + product.getIdDB(), null);

		long temp = -1;
		if (!dataExist(product, cursor)) {
			Utils.debug("insert 1111111111111 INSERT");
			/* Insert to database */
			/* Insert new */
			Cursor cursorInsert = _database.rawQuery(
					"INSERT INTO " + TABLE_NAME + "(" + COLUMN_CODE + ", "
							+ COLUMN_DESCRIPTION + ", " + COLUMN_GROUP + ", " + COLUMN_NAME + ", "
							+ COLUMN_PRICE + ", " + COLUMN_GROUP_ID
							+ ") values( ?, ?, ?, ?, ?, ?)",

					new String[] { product.getCode(), product.getDescription(),
							product.getGroup(), product.getName(), product.getPrice() + "",
							product.getGroupId() + "" });

			cursorInsert.moveToFirst();
			if (!cursorInsert.isAfterLast())
				temp = cursorInsert.getLong(cursorInsert
						.getColumnIndex(COLUMN_ID_DB));

			cursorInsert.close();
		} else {
			Utils.debug("insert 1111111111111 update");
			/* Update to database */
			Cursor cursorUpdate = _database.rawQuery(
					"UPDATE " + TABLE_NAME + " SET " + COLUMN_CODE + " = ?, "
							+ COLUMN_DESCRIPTION + " = ?, " + COLUMN_GROUP + " = ?, " + COLUMN_NAME
							+ " = ?, " + COLUMN_PRICE + " = ?, "
							+ COLUMN_GROUP_ID + " = ? " + "WHERE "
							+ COLUMN_ID_DB + " = " + product.getIdDB(),
					new String[] { product.getCode(), product.getDescription(),
						product.getGroup(), product.getName(), product.getPrice() + "",
							product.getGroupId() + "" });
			cursorUpdate.moveToFirst();
			if (!cursorUpdate.isAfterLast())
				temp = cursorUpdate.getLong(cursorUpdate
						.getColumnIndex(COLUMN_ID_DB));

			cursorUpdate.close();
		}
		cursor.close();
		close();
		return temp;
	}

	public void deleteAll() {
		open();

		System.out.println("project deleted all");
		// _database.rawQuery("DELETE FROM " + TABLE_NAME, null); not work
		// _database.rawQuery("DELETE FROM " + TABLE_NAME + " WHERE 1", null);
		_database.delete(TABLE_NAME, "1", null);
		close();
	}

	public List<Product> getAll() {
		open();

		List<Product> listProducts = new ArrayList<Product>();

		Cursor cursor = _database.query(TABLE_NAME, _allColumns, null, null,
				null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Product product = cursorToProduct(cursor);
			listProducts.add(product);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		close();
		return listProducts;
	}

	private Product cursorToProduct(Cursor cursor) {
		Product product = new Product();
		product.setIdDB(cursor.getLong(cursor.getColumnIndex(COLUMN_ID_DB)));
		product.setCode(cursor.getString(cursor.getColumnIndex(COLUMN_CODE)));
		product.setGroupId(cursor.getInt(cursor.getColumnIndex(COLUMN_GROUP_ID)));
		product.setGroup(cursor.getString(cursor.getColumnIndex(COLUMN_GROUP)));
		product.setPrice(cursor.getInt(cursor.getColumnIndex(COLUMN_PRICE)));
		product.setDescription(cursor.getString(cursor
				.getColumnIndex(COLUMN_DESCRIPTION)));
		product.setName(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));
		return product;
	}

	public final boolean isTableExist() {
		open();

		boolean isExist = false;
		Cursor cursor = _database.rawQuery(
				"select DISTINCT tbl_name from sqlite_master where tbl_name = '"
						+ TABLE_NAME + "'", null);
		isExist = cursor != null && cursor.getCount() > 0;

		cursor.close();
		close();

		return isExist;
	}
}
